﻿CREATE PROCEDURE [maint].[RotateDevice]
@DbName [sysname], @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = ' + ISNULL(@DbName,N'NULL')
		END
	
	DECLARE @BackupSets int
	SET @BackupSets = maint.GetBackupSets(@DbName)

	DECLARE @DeviceGroupName nvarchar(128)
	SET @DeviceGroupName = maint.GetDeviceGroupName(@DbName)

	DECLARE DEVICE_CUR CURSOR READ_ONLY FOR(
		SELECT [name], [phyname]
		FROM master.dbo.sysdevices
		WHERE  ([cntrltype] = 2)
			AND([name] LIKE (@DeviceGroupName + '%'))
	)
	ORDER BY [phyname] DESC
	OPEN DEVICE_CUR
	DECLARE @DeviceName sysname, @DevicePath nvarchar(256)
	FETCH NEXT FROM DEVICE_CUR INTO @DeviceName, @DevicePath
	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			IF(@OutputDiagnostics = 1)
				BEGIN
					PRINT N'	@DeviceName = ' + ISNULL(@DeviceName,N'NULL')
					PRINT N'	@DevicePath = ' + ISNULL(@DevicePath,N'NULL')
				END
	
			DECLARE @DeviceFileName NVARCHAR(256);
			SET @DeviceFileName = @DevicePath;
			EXEC [util].[RegExReplace] @input = @DeviceFileName OUT, @pattern='.*\\', @replacement='';
			
			IF(@OutputDiagnostics = 1)
				BEGIN
					PRINT N'	@DeviceFileName = ' + ISNULL(@DeviceFileName,N'NULL');
				END
			
			DECLARE @DeviceExtension nvarchar(6)
			SET @DeviceExtension = RIGHT(@DeviceFileName,CHARINDEX(N'.', REVERSE(@DeviceFileName)));
			
			DECLARE @DeviceNo int
			SET @DeviceNo = CAST(
				REPLACE(
					SUBSTRING(
						@DeviceFileName
						,CHARINDEX(@DeviceGroupName, @DeviceFileName) + LEN(@DeviceGroupName) + 1
						,LEN(@DeviceFileName)
					)
					,@DeviceExtension
					,N''
				)
			AS int)

			IF(@OutputDiagnostics = 1)
				PRINT N'	@DeviceNo = ' + ISNULL(CAST(@DeviceNo AS nvarchar),N'NULL')

			--remove devices outside of retension scope
			IF(@DeviceNo > @BackupSets)
				BEGIN
					EXEC @RC = master.dbo.sp_dropdevice @DeviceName, 'delfile'
					SET @ERROR = @@ERROR
					IF(@ERROR <> 0) OR (@RC <> 0) GOTO ERROR_HANDLER
					IF(@OutputDiagnostics = 1)
						PRINT N'	Device (' + ISNULL(@DeviceName,N'NULL') + N') dropped, file deleted'
						
					EXEC @RC = [maint].[DeleteDeviceHeader] @DeviceName;
					SET @ERROR = @@ERROR
					IF(@ERROR <> 0) OR (@RC <> 0) GOTO ERROR_HANDLER
					IF(@OutputDiagnostics = 1)
						PRINT N'	Device (' + ISNULL(@DeviceName,N'NULL') + N') header info deleted'

					GOTO NEXT_DEVICE
				END
			--ignore oldest device as it will be overwritten on next iteration
			ELSE IF(@DeviceNo = @BackupSets)
				GOTO NEXT_DEVICE

			--get parallel no before CURRENT token is swapped out
			DECLARE @ParallelDeviceNo int;
			SET @ParallelDeviceNo = CAST(SUBSTRING(@DeviceName,CHARINDEX(N'.',@DeviceName,1) + 1,LEN(@DeviceName)) AS int);
			--swap out CURRENT token and remove parallel no
			IF(@DeviceName LIKE '%_CURRENT.%')
				SET @DeviceName = @DeviceGroupName + N'_0';
			ELSE
				SET @DeviceName = SUBSTRING(@DeviceName,1, CHARINDEX(N'.',@DeviceName,1) - 1);

			DECLARE @NewDeviceName sysname, @NewDevicePath nvarchar(256)
			SET @NewDeviceName = REPLACE(@DeviceName,@DeviceNo,@DeviceNo + 1)
			SET @NewDevicePath = REPLACE(@DevicePath,@DeviceName,@NewDeviceName)

			--delete existing file
			----------------------
			DECLARE @Exists int
			EXEC master.dbo.xp_fileexist @NewDevicePath, @Exists OUTPUT
			IF(@Exists = 1)
				BEGIN
					DECLARE @DelCmd nvarchar(256)
					SET @DelCmd = N'DEL "' + @NewDevicePath + N'"'
					IF(@OutputDiagnostics = 1)
						
						PRINT 'DEL Command: ' + @DelCmd
					EXEC @RC = master.dbo.xp_cmdshell @DelCmd, NO_OUTPUT
					SET @ERROR = @@ERROR
					IF(@ERROR <> 0) OR (@RC <> 0) GOTO ERROR_HANDLER
				END

			--rename physical file and update header info
			---------------------------------------------
			EXEC master.dbo.xp_fileexist @DevicePath, @Exists OUTPUT
			IF(@Exists = 1)
				BEGIN
					DECLARE @RenameCmd nvarchar(512)
					SET @RenameCmd = N'RENAME "' + @DevicePath + N'" "' + @NewDeviceName + @DeviceExtension + '"'
					IF(@OutputDiagnostics = 1)
						PRINT 'RENAME Command: ' + @RenameCmd
					EXEC @RC = master.dbo.xp_cmdshell @RenameCmd, NO_OUTPUT
					SET @ERROR = @@ERROR
					IF(@ERROR <> 0) OR (@RC <> 0) GOTO ERROR_HANDLER
					
					EXEC @RC = [maint].[UpdateDeviceHeader] @NewDeviceName, @DeviceName;
					SET @ERROR = @@ERROR
					IF(@ERROR <> 0) OR (@RC <> 0) GOTO ERROR_HANDLER
					IF(@OutputDiagnostics = 1)
						PRINT N'	Device (' + ISNULL(@NewDeviceName,N'NULL') + N') header info updated'
				END

			--add logical device
			--------------------
			--append parallel device no.(device path already has it appended to extension)
			SET @NewDeviceName = @NewDeviceName + N'.' + CAST(@ParallelDeviceNo AS nvarchar);

			IF NOT EXISTS(SELECT * FROM master.dbo.sysdevices WHERE([name] = @NewDeviceName))
				BEGIN
					EXEC @RC = master.dbo.sp_addumpdevice 'disk', @NewDeviceName, @NewDevicePath
					SET @ERROR = @@ERROR
					IF(@ERROR <> 0) OR (@RC <> 0) GOTO ERROR_HANDLER
					IF(@OutputDiagnostics = 1)
						PRINT N'	@NewDeviceName = ' + ISNULL(@NewDeviceName,N'NULL')
				END

			--update backup history
			-----------------------
			UPDATE msdb.dbo.backupmediafamily
			SET physical_device_name = @NewDevicePath
			WHERE (physical_device_name = @DevicePath);

NEXT_DEVICE:
			FETCH NEXT FROM DEVICE_CUR INTO @DeviceName, @DevicePath
		END
	CLOSE DEVICE_CUR
	DEALLOCATE DEVICE_CUR

GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC = 0)
		SET @RC = @ERROR

EXIT_PROC:
	IF(CURSOR_STATUS('global','DEVICE_CUR') >= 0)
		BEGIN
			CLOSE DEVICE_CUR
			DEALLOCATE DEVICE_CUR
		END

	RETURN(@RC)
END